import pymysql


def Create_conn():
    # conn = pymysql.connect('localhost', user="root", passwd="lihongchen0531")
    conn = pymysql.connect('rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com', user="lhc_0531",
                           passwd="@lihongchen0531")  # 打开数据库连接
    return conn

def Connect_table():
    # conn = pymysql.connect('localhost', user="root", passwd="lihongchen0531", db="Bus_priority")
    conn = pymysql.connect('rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com', user="lhc_0531",
                           passwd="@lihongchen0531", db="Bus_priority")  # 连接到表
    return conn


def Create_DB():
    conn = Create_conn()
    cursor = conn.cursor()
    # 创建pythonBD数据库
    cursor.execute('CREATE DATABASE IF NOT EXISTS Bus_priority DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
    conn = pymysql.connect('rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com', user="lhc_0531", passwd="@lihongchen0531", db="Bus_priority")
    cursor = conn.cursor()  # 获取游标
    # 创建路口表
    # 路口编号、相位、道路组成、初始时间
    sql = """
    create table if not exists Crossing(
        Crossing_id char (4) primary key,
        E_roadid char (16),
        W_roadid char (16),
        S_roadid char (16),
        N_roadid char (16),
        EW_straight int (8),
        EW_left int (8),
        NS_straight int (8),
        NS_left int (8),
        Initial_time int (8)
    );"""
    cursor.execute(sql)

    # 创建道路表
    # 道路编号、道路名称、流量、左右直行流量
    sql = """
    create table if not exists Road(
        Road_id char (4) primary key,
        Road_name char (16),
        Total_flow int (8),
        L_flow int (8),
        R_flow int (8),
        S_flow int (8)
    );"""
    cursor.execute(sql)

    # 创建公交车表
    # 公交车编号、公交车位置、乘客数量、延误时间
    sql = """
        create table if not exists Bus(
            Bus_id char (4) primary key ,
            Road_id char (4),
            Passager_num int (8),
            Delay_time int (8),
            x_coordinate char (64),
            y_coordinate char (64),
            foreign key (Road_id) references Road(Road_id)
        );"""
    cursor.execute(sql)

    # 创建请求表
    # 请求路口编号、请求公交车编号、请求时间、请求相位、处理策略
    sql = """
    create table if not exists Request(
        Crossing_id char (4),
        Bus_id char (4),
        RTime Datetime,
        Strategy char(20),
        Request_stage char (20),
        primary key (Crossing_id, Bus_id),
        foreign key (Crossing_id) references Crossing(Crossing_id),
        foreign key (Bus_id) references Bus(Bus_id)
    );"""
    cursor.execute(sql)

    # 创建公交车站表
    # 车站编号、车站名称、车站xy坐标
    sql = """
    create table if not exists Station(
        Station_id char (4),
        Station_name char (64),
        Station_x char (64),
        Station_y char (64),
        primary key (Station_id)
    );"""
    cursor.execute(sql)

    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return 0


def Init_DB():
    """
    初始化数据库，清除所有数据
    :return: 
    """
    try:
        conn = Create_conn()
        cursor = conn.cursor()  # 获取游标
        sql = 'drop database if exists Bus_priority '
        cursor.execute(sql)
        conn.commit()  # 提交
        cursor.close()  # 关闭游标
        conn.close()  # 退出连接
    except:
        Create_DB()
    Create_DB()


def add_Crossing(args):
    """
    添加路口信息
    :param args: 按照顺序分别是：id、东西南北id、信号相位、初始时间
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = 'insert into bus_priority.Crossing value (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
    cursor.execute(sql, args)
    conn.commit()  # 提交
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接


def add_Road(args):
    """
    添加道路信息
    :param args: 按照顺序分别是：道路编号、道路名称、流量、左右直行流量
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = 'insert into bus_priority.Road value (%s, %s, %s, %s, %s, %s);'
    cursor.execute(sql, args)
    conn.commit()  # 提交
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接


def add_Bus(args):
    """
    添公交车信息
    :param args: 按照顺序分别是：公交车编号、公交车位置、乘客数量、延误时间
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = 'insert into bus_priority.Bus value (%s, %s, %s, %s, %s, %s);'
    cursor.execute(sql, args)
    conn.commit()  # 提交
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接


def add_Request(args):
    """
    添加请求信息
    :param args: 按照顺序分别是：请求路口编号、请求公交车编号、请求时间、请求相位、处理策略
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = 'insert into bus_priority.Request value (%s, %s, %s, %s, %s);'
    cursor.execute(sql, args)
    conn.commit()  # 提交
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接


def add_Station(args):
    """
    添加站点信息
    :param args:
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = 'insert into bus_priority.Station value (%s, %s, %s, %s);'
    cursor.execute(sql, args)
    conn.commit()  # 提交
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接


def OC_Filling():
    """
    一键填充数据库，数据来源'data/data.xlsx'
    :return:
    """
    import xlrd
    from datetime import datetime
    from xlrd import xldate_as_tuple
    workBook = xlrd.open_workbook('data/data.xlsx')
    sheet_name = ['road', 'bus', 'crossing', 'station']
    for item in sheet_name:
        sheet = workBook.sheet_by_name(item)
        for i in range(1, sheet.nrows):
            mess = (tuple(sheet.row_values(i)))
            if item == 'road':
                add_Road(mess)
            elif item == 'bus':
                add_Bus(mess)
            elif item == 'crossing':
                add_Crossing(mess)
            elif item == 'station':
                add_Station(mess)

    sheet = workBook.sheet_by_name('request')
    # 由于request中RTime为时间类型，需要单独进行转换，参考：
    # https://www.cnblogs.com/xxiong1031/archive/2004/01/13/7069006.html
    for i in range(1, sheet.nrows):
        mess = sheet.row_values(i)
        cell = sheet.cell_value(i, 2)
        date = datetime(*xldate_as_tuple(cell, 0))
        cell = date.strftime('%Y/%m/%d %H:%M:%S')
        mess[2] = cell
        mess = tuple(mess)
        try:
            add_Request(mess)
        except:
            print('插入失败，可能未满足外键约束')


def Read_Bus():
    """
    读取公交车信息
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT * FROM bus_priority.bus;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return results


def Read_Request():
    """
    读取公交车信息
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT * FROM bus_priority.request;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return results


def Read_Road():
    """
    读取道路信息
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT * FROM bus_priority.road;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return results


def Read_Crossing():
    """
    读取交叉口信息
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT * FROM bus_priority.crossing;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return results


def Read_Station():
    """
    读取交叉口信息
    :return:
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT * FROM bus_priority.station;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return results


def Update_Road(id):
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "update bus_priority.crossing" \
          "set Passager_num 10" \
          "where Bus_id = 1"
    cursor.execute(sql)
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return 0


def Update_Bus():
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "update bus_priority.bus" \
          "set Passager_num 10" \
          "where Bus_id = 1"
    cursor.execute(sql)
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    return 0


def Get_Col_Name(table_name):
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "select column_name from information_schema.columns " \
          "where table_name="
    sql = sql + "'" + table_name + "'" + ' ORDER BY ordinal_position;'
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    col_name = []
    for item in results:
        col_name.append(str(item[0]))
    return col_name


def GET_all_Road_id():
    """
    获取道路主键：id
    :return: list, 升序排列，内元素str
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT Road_id FROM bus_priority.road;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    ans_list = []
    for item in results:
        ans_list.append(int(item[0]))
    ans_list.sort()
    return [str(x) for x in ans_list]


def GET_all_Bus_id():
    """
    获取公交车主键：id
    :return: list, 升序排列，内元素str
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT Bus_id FROM bus_priority.bus;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()  # 先关闭游标
    conn.close()  # 再关闭数据库连接
    ans_list = []
    for item in results:
        ans_list.append(int(item[0]))
    ans_list = list(set(ans_list))  # 去重，按理说id作为主键，不会重复
    ans_list.sort()
    return [str(x) for x in ans_list]


def GET_all_Crossing_id():
    """
    获取路口主键：id
    :return:list, 升序排列，内元素str
    """
    conn = Connect_table()
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT Crossing_id FROM bus_priority.crossing;"
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    ans_list = []
    for item in results:
        ans_list.append(int(item[0]))
    ans_list = list(set(ans_list))  # 去重，按理说id作为主键，不会重复
    ans_list.sort()
    return [str(x) for x in ans_list]


if __name__ == '__main__':
    # Init_DB()
    # OC_Filling()
    print(Read_Bus())
    print(Read_Crossing())
    print(Read_Request())
    print(Get_Col_Name('bus'))
    print(GET_all_Crossing_id())
    print(Read_Station())
